package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.dto.CarPortCountDto;

public class CarPortCountDaoImp extends ViewListDao implements CarPortCountDao {

	protected CarPortCountDaoImp(int pageNo,
			int maxIndex,int sortKey,boolean asc) throws TmsDaoException{
		super( pageNo,maxIndex, sortKey, asc);
	}
	
	
	public CarPortCountDaoImp() throws TmsDaoException{
		super();
	}

	/* (non-Javadoc)
	 * @see com.tianqu.tms.dao.CarPortCountDao#loadCarPortCount()
	 */
	@Override
	public List<CarPortCountDto> loadCarPortCount(int yy) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<CarPortCountDto> carPortCountList = new ArrayList<CarPortCountDto>();
		StringBuffer sql = new StringBuffer("");
		
		sql.append("SELECT ");
		sql.append("INVENTORY.MID AS MEMID, ");
		sql.append("MEMBER.REALNAME AS SHOPNAME, ");//关联会员表
		sql.append("MEMBER.CARPORT AS CARPORT, ");
		sql.append("MEMBER.USEDCARPORT AS USEDCARPORT, ");
		sql.append("INVENTORY.TODAYINVENTORY AS TODAYINVENTORY, ");
		sql.append("INVENTORY.YESTERDAYINVENTORY AS YESTERDAYINVENTORY, ");	
		sql.append("INVENTORY.THISWEEKINVENTORY AS THISWEEKINVENTORY, ");
		sql.append("INVENTORY.THISMONTHINVENTORY AS THISMONTHINVENTORY, ");
		sql.append("INVENTORY.ONEQUARTERINVENTORY AS ONEQUARTERINVENTORY, "); 
		sql.append("INVENTORY.TWOQUARTERINVENTORY AS TWOQUARTERINVENTORY, ");
		sql.append("INVENTORY.THREEQUARTERINVENTORY AS THREEQUARTERINVENTORY, ");
		sql.append("INVENTORY.FOURQUARTERINVENTORY AS FOURQUARTERINVENTORY ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_MEMBER_INVENTORY);
		sql.append(" INVENTORY ");
		sql.append(" LEFT JOIN  ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" MEMBER ON MEMBER.MEMBERID = INVENTORY.MID ");
		sql.append(" LEFT JOIN ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append(" USER ON MEMBER.USERID = USER.USERID");
		sql.append(" WHERE INVENTORY.YEARS = "+yy+" ");
		sql.append(" AND USER.FLAG <>2");
		sql.append(" ORDER BY CARPORT DESC");
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			int i = 0;
			while(rs.next()){
				CarPortCountDto dto = new CarPortCountDto();
				dto.setMemId(rs.getString("MEMID"));
				dto.setShopName(rs.getString("SHOPNAME"));
				
				dto.setCarPort(String.valueOf(rs.getInt("CARPORT")));
				
				if(rs.getInt("USEDCARPORT") == 0)
				{
					dto.setUsedCarPort("-"); 
				}else{
					dto.setUsedCarPort(String.valueOf(rs.getInt("USEDCARPORT"))); 
				}
			
				if(rs.getInt("THISMONTHINVENTORY") == 0)
				{
					dto.setMonthInner("-");
				}else{
					dto.setMonthInner(String.valueOf(rs.getInt("THISMONTHINVENTORY")));
				}
				if(rs.getInt("FOURQUARTERINVENTORY") == 0)
				{
					dto.setSeasonFourInner("-");
				}else{
					dto.setSeasonFourInner(String.valueOf(rs.getInt("FOURQUARTERINVENTORY")));
				}
				if(rs.getInt("ONEQUARTERINVENTORY") == 0)
				{
					dto.setSeasonOneInner("-");
				}else{
					dto.setSeasonOneInner(String.valueOf(rs.getInt("ONEQUARTERINVENTORY")));
				}
				if(rs.getInt("THREEQUARTERINVENTORY") == 0)
				{
					dto.setSeasonThreeInner("-");
				}else{
					dto.setSeasonThreeInner(String.valueOf(rs.getInt("THREEQUARTERINVENTORY")));
				}
				if(rs.getInt("TWOQUARTERINVENTORY") == 0)
				{
					dto.setSeasonTwoInner("-");
				}else{
					dto.setSeasonTwoInner(String.valueOf(rs.getInt("TWOQUARTERINVENTORY")));
				}
				if(rs.getInt("TODAYINVENTORY") == 0)
				{
					dto.setTodayInner("-");
				}else{
					dto.setTodayInner(String.valueOf(rs.getInt("TODAYINVENTORY")));
				}
				if(rs.getInt("TODAYINVENTORY") == 0)
				{
					dto.setTodayInner("-");
				}else{
					dto.setTodayInner(String.valueOf(rs.getInt("TODAYINVENTORY")));
				}
				if(rs.getInt("THISWEEKINVENTORY") == 0)
				{
					dto.setWeekInner("-");
				}else{
					dto.setWeekInner(String.valueOf(rs.getInt("THISWEEKINVENTORY")));
				}
				if(rs.getInt("YESTERDAYINVENTORY") == 0)
				{
					dto.setYesterdayInner("-");
				}else{
					dto.setYesterdayInner(String.valueOf(rs.getInt("YESTERDAYINVENTORY")));
				}
				
				if(i%2 == 0){
					dto.setCssFlag(1);
				}else{
					dto.setCssFlag(2);
				}
				int num = i+1;
				dto.setNum(num);
				carPortCountList.add(dto);
				i++;
			}
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return carPortCountList;
	}


	@Override
	public List<Integer> countCarInfo() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		final List<Integer> r = new ArrayList<Integer>();
		final StringBuffer sql = new StringBuffer("");
		
		sql.append(" SELECT SUM(usedCarPort) AS COUNT FROM ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" UNION ALL");
		sql.append(" SELECT COUNT(serial) FROM ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
		sql.append(" WHERE REGISTERSTATUS=2 OR REGISTERSTATUS=6");
		sql.append(" UNION ALL ");
		sql.append(" SELECT COUNT(serial) FROM ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while(rs.next()){
				r.add(rs.getInt("COUNT"));
			}
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return r;
	}


	@Override
	public List<CarPortCountDto> countCarInfoByFloor(int floor) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		final List<CarPortCountDto> r = new ArrayList<CarPortCountDto>();
		final StringBuffer sql = new StringBuffer("");
		
		sql.append(" SELECT ");
		sql.append(" MEMBER.memberId AS MEMBERID,");
		sql.append(" MEMBER.shortName AS SHORTNAME,");
		sql.append(" MEMBER.flag AS FLAG,");
		sql.append(" MEMBER.carPort AS CARPORT,");
		sql.append(" MEMBER.usedCarPort AS USEDCARPORT,");
		sql.append(" COUNT(1) AS TOTALCOUNT,");
		sql.append(" SUM(CASE WHEN INFO.registerStatus=1 THEN 1 ELSE 0 END) AS INCOUNT,");
		sql.append(" SUM(CASE WHEN INFO.registerStatus=2 THEN 1 ELSE 0 END) AS OUTCOUNT,");
		sql.append(" SUM(CASE WHEN INFO.registerStatus=3 THEN 1 ELSE 0 END) AS EXPIREDCOUNT,");
		sql.append(" SUM(CASE WHEN INFO.registerStatus=4 THEN 1 ELSE 0 END) AS ARCHIVECOUNT,");
		sql.append(" SUM(CASE WHEN INFO.barCodeNum=1 THEN 1 ELSE 0 END) AS PENDINGCOUNT,");
		sql.append(" SUM(CASE WHEN INFO.registerStatus=6 THEN 1 ELSE 0 END) AS REGISTERCOUNT");
		sql.append(" FROM (");
		sql.append(" SELECT A.memberId AS memberId,");
		sql.append(" A.shortName AS shortName,");
		sql.append(" B.flag AS flag,");
		sql.append(" A.carPort AS carPort,");
		sql.append(" A.usedCarPort AS usedCarPort");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_MEMBER);
		sql.append(" A LEFT JOIN ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append(" B ON A.userId = B.userId ");
		sql.append(" WHERE B.flag != 2 ");
		sql.append(" AND find_in_set(" + floor);
		sql.append(",A.floors)");
		sql.append(" ) AS MEMBER");
		sql.append(" LEFT JOIN ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
		sql.append(" AS INFO");
		sql.append(" ON MEMBER.memberId = INFO.memberId");
		sql.append(" GROUP BY MEMBER.memberId;");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			int i = 0;
			while(rs.next()){
				final CarPortCountDto dto = new CarPortCountDto();
				if(i%2 == 0){
					dto.setCssFlag(1);
				}else{
					dto.setCssFlag(2);
				}
				int num = i+1;
				dto.setNum(num);
				dto.setCarScore(floor);
				dto.setFlag(rs.getString("FLAG"));
				dto.setMemId(rs.getString("MEMBERID"));
				dto.setCarPort(rs.getString("CARPORT"));
				dto.setShortName(rs.getString("SHORTNAME"));
				dto.setUsedCarPort(rs.getString("USEDCARPORT"));
				dto.setInCount(rs.getInt("INCOUNT"));
				dto.setOutCount(rs.getInt("OUTCOUNT"));
				dto.setTotalCount(rs.getInt("TOTALCOUNT"));
				dto.setExpiredCount(rs.getInt("EXPIREDCOUNT"));
				dto.setArchiveCount(rs.getInt("ARCHIVECOUNT"));
				dto.setPendingCount(rs.getInt("PENDINGCOUNT"));
				dto.setRegisterCount(rs.getInt("REGISTERCOUNT"));
				int userCarPort = 0;
				int carPort = 0;
				if (!StringUtil.isNullOrBlank(dto.getUsedCarPort()))  userCarPort = Integer.parseInt(dto.getUsedCarPort());
				if (!StringUtil.isNullOrBlank(dto.getCarPort()))  carPort = Integer.parseInt(dto.getCarPort());
				if ( userCarPort > carPort) {
					dto.setWarnFlag(2);
				} else {
					dto.setWarnFlag(1);
				}
				r.add(dto); i++;
			}
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return r;
	}
	
	

}
